--    Program: 		CAPARIMP_SYSTEM_OPTION.sql
--    Description:  Import AR System Option information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
                    		                 		
   cursor cur_system_option is
      select * from CRST_AR_SYSTEM_PARAMETERS_ALL;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_system_option loop   	
     l_err_msg_tmp := '';
     
     begin
       -- import data
       insert into AR_SYSTEM_PARAMETERS_ALL 
       (
         SET_OF_BOOKS_ID                 ,
         CREATED_BY                      ,
         CREATION_DATE                   ,
         LAST_UPDATED_BY                 ,
         LAST_UPDATE_DATE                ,
         LAST_UPDATE_LOGIN               ,
         ACCOUNTING_METHOD               ,
         ACCRUE_INTEREST                 ,
         UNEARNED_DISCOUNT               ,
         PARTIAL_DISCOUNT_FLAG           ,
         PRINT_REMIT_TO                  ,
         DEFAULT_CB_DUE_DATE             ,
         AUTO_SITE_NUMBERING             ,
         CASH_BASIS_SET_OF_BOOKS_ID      ,
         CODE_COMBINATION_ID_GAIN        ,
         AUTOCASH_HIERARCHY_ID           ,
         RUN_GL_JOURNAL_IMPORT_FLAG      ,
         CER_SPLIT_AMOUNT                ,
         CER_DSO_DAYS                    ,
         POSTING_DAYS_PER_CYCLE          ,
         ADDRESS_VALIDATION              ,
         ATTRIBUTE1                      ,
         ATTRIBUTE2                      ,
         ATTRIBUTE_CATEGORY              ,
         ATTRIBUTE3                      ,
         ATTRIBUTE4                      ,
         ATTRIBUTE5                      ,
         ATTRIBUTE6                      ,
         ATTRIBUTE7                      ,
         ATTRIBUTE8                      ,
         ATTRIBUTE9                      ,
         ATTRIBUTE10                     ,
         CALC_DISCOUNT_ON_LINES_FLAG     ,
         CHANGE_PRINTED_INVOICE_FLAG     ,
         CODE_COMBINATION_ID_LOSS        ,
         CREATE_RECIPROCAL_FLAG          ,
         DEFAULT_COUNTRY                 ,
         DEFAULT_TERRITORY               ,
         GENERATE_CUSTOMER_NUMBER        ,
         INVOICE_DELETION_FLAG           ,
         LOCATION_STRUCTURE_ID           ,
         SITE_REQUIRED_FLAG              ,
         TAX_ALLOW_COMPOUND_FLAG         ,
         TAX_INVOICE_PRINT               ,
         TAX_METHOD                      ,
         TAX_USE_CUSTOMER_EXEMPT_FLAG    ,
         TAX_USE_CUST_EXC_RATE_FLAG      ,
         TAX_USE_LOC_EXC_RATE_FLAG       ,
         TAX_USE_PRODUCT_EXEMPT_FLAG     ,
         TAX_USE_PROD_EXC_RATE_FLAG      ,
         TAX_USE_SITE_EXC_RATE_FLAG      ,
         AI_LOG_FILE_MESSAGE_LEVEL       ,
         AI_MAX_MEMORY_IN_BYTES          ,
         AI_ACCT_FLEX_KEY_LEFT_PROMPT    ,
         AI_MTL_ITEMS_KEY_LEFT_PROMPT    ,
         AI_TERRITORY_KEY_LEFT_PROMPT    ,
         AI_PURGE_INTERFACE_TABLES_FLAG  ,
         AI_ACTIVATE_SQL_TRACE_FLAG      ,
         DEFAULT_GROUPING_RULE_ID        ,
         SALESREP_REQUIRED_FLAG          ,
         ATTRIBUTE11                     ,
         ATTRIBUTE12                     ,
         ATTRIBUTE13                     ,
         ATTRIBUTE14                     ,
         ATTRIBUTE15                     ,
         AUTO_REC_INVOICES_PER_COMMIT    ,
         AUTO_REC_RECEIPTS_PER_COMMIT    ,
         PAY_UNRELATED_INVOICES_FLAG     ,
         PRINT_HOME_COUNTRY_FLAG         ,
         LOCATION_TAX_ACCOUNT,
         FROM_POSTAL_CODE                ,
         TO_POSTAL_CODE                  ,
         TAX_REGISTRATION_NUMBER         ,
         POPULATE_GL_SEGMENTS_FLAG       ,
         UNALLOCATED_REVENUE_CCID,
         ORG_ID                          ,
         TAX_CURRENCY_CODE               ,
         TAX_PRECISION                   ,
         TAX_MINIMUM_ACCOUNTABLE_UNIT    ,
         TAX_HEADER_LEVEL_FLAG           ,
         TAX_ROUNDING_RULE               ,
         TAX_ROUNDING_ALLOW_OVERRIDE     ,
         TAX_CODE                        ,
         GLOBAL_ATTRIBUTE1               ,
         GLOBAL_ATTRIBUTE2               ,
         GLOBAL_ATTRIBUTE3               ,
         GLOBAL_ATTRIBUTE4               ,
         GLOBAL_ATTRIBUTE5               ,
         GLOBAL_ATTRIBUTE6               ,
         GLOBAL_ATTRIBUTE7               ,
         GLOBAL_ATTRIBUTE8               ,
         GLOBAL_ATTRIBUTE9               ,
         GLOBAL_ATTRIBUTE10              ,
         GLOBAL_ATTRIBUTE11              ,
         GLOBAL_ATTRIBUTE12              ,
         GLOBAL_ATTRIBUTE13              ,
         GLOBAL_ATTRIBUTE14              ,
         GLOBAL_ATTRIBUTE15              ,
         GLOBAL_ATTRIBUTE16              ,
         GLOBAL_ATTRIBUTE17              ,
         GLOBAL_ATTRIBUTE18              ,
         GLOBAL_ATTRIBUTE19              ,
         GLOBAL_ATTRIBUTE20              ,
         GLOBAL_ATTRIBUTE_CATEGORY       ,
         RULE_SET_ID                     ,
         TAX_USE_ACCOUNT_EXC_RATE_FLAG   ,
         TAX_USE_SYSTEM_EXC_RATE_FLAG    ,
         TAX_HIER_SITE_EXC_RATE          ,
         TAX_HIER_CUST_EXC_RATE          ,
         TAX_HIER_PROD_EXC_RATE          ,
         TAX_HIER_ACCOUNT_EXC_RATE       ,
         TAX_HIER_SYSTEM_EXC_RATE        ,
         TAX_ENFORCE_ACCOUNT_FLAG        ,
         TAX_DATABASE_VIEW_SET           ,
         INCLUSIVE_TAX_USED              ,
         CODE_COMBINATION_ID_ROUND       ,
         TRX_HEADER_LEVEL_ROUNDING       ,
         TRX_HEADER_ROUND_CCID           ,
         FINCHRG_RECEIVABLES_TRX_ID      ,
         SALES_TAX_GEOCODE               ,
         BILLS_RECEIVABLE_ENABLED_FLAG   ,
         TA_INSTALLED_FLAG               ,
         REV_TRANSFER_CLEAR_CCID         ,
         SALES_CREDIT_PCT_LIMIT          ,
         MAX_WRTOFF_AMOUNT               ,
         IREC_CC_RECEIPT_METHOD_ID       ,
         SHOW_BILLING_NUMBER_FLAG        ,
         CROSS_CURRENCY_RATE_TYPE        ,
         DOCUMENT_SEQ_GEN_LEVEL          ,
         CALC_TAX_ON_CREDIT_MEMO_FLAG    ,
         IREC_BA_RECEIPT_METHOD_ID       ,
         PAYMENT_THRESHOLD               ,
         STANDARD_REFUND                 ,
         CREDIT_CLASSIFICATION1          ,
         CREDIT_CLASSIFICATION3          ,
         CREDIT_CLASSIFICATION2
       )
       values
       (
         CAPBRIMP_MAPPING.GetSOBIDMapping(cur.SET_OF_BOOKS_ID),
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.CREATION_DATE                   ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
         cur.LAST_UPDATE_DATE                ,
         cur.LAST_UPDATE_LOGIN               ,
         cur.ACCOUNTING_METHOD               ,
         cur.ACCRUE_INTEREST                 ,
         cur.UNEARNED_DISCOUNT               ,
         cur.PARTIAL_DISCOUNT_FLAG           ,
         cur.PRINT_REMIT_TO                  ,
         cur.DEFAULT_CB_DUE_DATE             ,
         cur.AUTO_SITE_NUMBERING             ,
         cur.CASH_BASIS_SET_OF_BOOKS_ID      , --NULL
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID_GAIN),
         cur.AUTOCASH_HIERARCHY_ID           ,
         cur.RUN_GL_JOURNAL_IMPORT_FLAG      ,
         cur.CER_SPLIT_AMOUNT                ,
         cur.CER_DSO_DAYS                    ,
         cur.POSTING_DAYS_PER_CYCLE          ,
         cur.ADDRESS_VALIDATION              ,
         cur.ATTRIBUTE1                      ,
         cur.ATTRIBUTE2                      ,
         cur.ATTRIBUTE_CATEGORY              ,
         cur.ATTRIBUTE3                      ,
         cur.ATTRIBUTE4                      ,
         cur.ATTRIBUTE5                      ,
         cur.ATTRIBUTE6                      ,
         cur.ATTRIBUTE7                      ,
         cur.ATTRIBUTE8                      ,
         cur.ATTRIBUTE9                      ,
         cur.ATTRIBUTE10                     ,
         cur.CALC_DISCOUNT_ON_LINES_FLAG     ,
         cur.CHANGE_PRINTED_INVOICE_FLAG     ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID_LOSS),
         cur.CREATE_RECIPROCAL_FLAG          ,
         cur.DEFAULT_COUNTRY                 ,
         cur.DEFAULT_TERRITORY               ,
         cur.GENERATE_CUSTOMER_NUMBER        ,
         cur.INVOICE_DELETION_FLAG           ,
         cur.LOCATION_STRUCTURE_ID           ,
         cur.SITE_REQUIRED_FLAG              ,
         cur.TAX_ALLOW_COMPOUND_FLAG         ,
         cur.TAX_INVOICE_PRINT               ,
         cur.TAX_METHOD                      ,
         cur.TAX_USE_CUSTOMER_EXEMPT_FLAG    ,
         cur.TAX_USE_CUST_EXC_RATE_FLAG      ,
         cur.TAX_USE_LOC_EXC_RATE_FLAG       ,
         cur.TAX_USE_PRODUCT_EXEMPT_FLAG     ,
         cur.TAX_USE_PROD_EXC_RATE_FLAG      ,
         cur.TAX_USE_SITE_EXC_RATE_FLAG      ,
         cur.AI_LOG_FILE_MESSAGE_LEVEL       ,
         cur.AI_MAX_MEMORY_IN_BYTES          ,
         cur.AI_ACCT_FLEX_KEY_LEFT_PROMPT    ,
         cur.AI_MTL_ITEMS_KEY_LEFT_PROMPT    ,
         cur.AI_TERRITORY_KEY_LEFT_PROMPT    ,
         cur.AI_PURGE_INTERFACE_TABLES_FLAG  ,
         cur.AI_ACTIVATE_SQL_TRACE_FLAG      ,
         cur.DEFAULT_GROUPING_RULE_ID        ,
         cur.SALESREP_REQUIRED_FLAG          ,
         cur.ATTRIBUTE11                     ,
         cur.ATTRIBUTE12                     ,
         cur.ATTRIBUTE13                     ,
         cur.ATTRIBUTE14                     ,
         cur.ATTRIBUTE15                     ,
         cur.AUTO_REC_INVOICES_PER_COMMIT    ,
         cur.AUTO_REC_RECEIPTS_PER_COMMIT    ,
         cur.PAY_UNRELATED_INVOICES_FLAG     ,
         cur.PRINT_HOME_COUNTRY_FLAG         ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.LOCATION_TAX_ACCOUNT),
         cur.FROM_POSTAL_CODE                ,
         cur.TO_POSTAL_CODE                  ,
         cur.TAX_REGISTRATION_NUMBER         ,
         cur.POPULATE_GL_SEGMENTS_FLAG       ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.UNALLOCATED_REVENUE_CCID),
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
         cur.TAX_CURRENCY_CODE               ,
         cur.TAX_PRECISION                   ,
         cur.TAX_MINIMUM_ACCOUNTABLE_UNIT    ,
         cur.TAX_HEADER_LEVEL_FLAG           ,
         cur.TAX_ROUNDING_RULE               ,
         cur.TAX_ROUNDING_ALLOW_OVERRIDE     ,
         cur.TAX_CODE                        ,
         cur.GLOBAL_ATTRIBUTE1               ,
         cur.GLOBAL_ATTRIBUTE2               ,
         cur.GLOBAL_ATTRIBUTE3               ,
         cur.GLOBAL_ATTRIBUTE4               ,
         cur.GLOBAL_ATTRIBUTE5               ,
         cur.GLOBAL_ATTRIBUTE6               ,
         cur.GLOBAL_ATTRIBUTE7               ,
         cur.GLOBAL_ATTRIBUTE8               ,
         cur.GLOBAL_ATTRIBUTE9               ,
         cur.GLOBAL_ATTRIBUTE10              ,
         cur.GLOBAL_ATTRIBUTE11              ,
         cur.GLOBAL_ATTRIBUTE12              ,
         cur.GLOBAL_ATTRIBUTE13              ,
         cur.GLOBAL_ATTRIBUTE14              ,
         cur.GLOBAL_ATTRIBUTE15              ,
         cur.GLOBAL_ATTRIBUTE16              ,
         cur.GLOBAL_ATTRIBUTE17              ,
         cur.GLOBAL_ATTRIBUTE18              ,
         cur.GLOBAL_ATTRIBUTE19              ,
         cur.GLOBAL_ATTRIBUTE20              ,
         cur.GLOBAL_ATTRIBUTE_CATEGORY       ,
         cur.RULE_SET_ID                     ,
         cur.TAX_USE_ACCOUNT_EXC_RATE_FLAG   ,
         cur.TAX_USE_SYSTEM_EXC_RATE_FLAG    ,
         cur.TAX_HIER_SITE_EXC_RATE          ,
         cur.TAX_HIER_CUST_EXC_RATE          ,
         cur.TAX_HIER_PROD_EXC_RATE          ,
         cur.TAX_HIER_ACCOUNT_EXC_RATE       ,
         cur.TAX_HIER_SYSTEM_EXC_RATE        ,
         cur.TAX_ENFORCE_ACCOUNT_FLAG        ,
         cur.TAX_DATABASE_VIEW_SET           ,
         cur.INCLUSIVE_TAX_USED              ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.CODE_COMBINATION_ID_ROUND),
         cur.TRX_HEADER_LEVEL_ROUNDING       ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.TRX_HEADER_ROUND_CCID),
         cur.FINCHRG_RECEIVABLES_TRX_ID      ,
         cur.SALES_TAX_GEOCODE               ,
         cur.BILLS_RECEIVABLE_ENABLED_FLAG   ,
         cur.TA_INSTALLED_FLAG               ,
         CAPBRIMP_MAPPING.GetCCIDMapping(cur.REV_TRANSFER_CLEAR_CCID),
         cur.SALES_CREDIT_PCT_LIMIT          ,
         cur.MAX_WRTOFF_AMOUNT               ,
         cur.IREC_CC_RECEIPT_METHOD_ID       ,
         cur.SHOW_BILLING_NUMBER_FLAG        ,
         cur.CROSS_CURRENCY_RATE_TYPE        ,
         cur.DOCUMENT_SEQ_GEN_LEVEL          ,
         cur.CALC_TAX_ON_CREDIT_MEMO_FLAG    ,
         cur.IREC_BA_RECEIPT_METHOD_ID       ,
         cur.PAYMENT_THRESHOLD               ,
         cur.STANDARD_REFUND                 ,
         cur.CREDIT_CLASSIFICATION1          ,
         cur.CREDIT_CLASSIFICATION3          ,
         cur.CREDIT_CLASSIFICATION2
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

      if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_SYSTEM_OPTION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit